package softtech.apps.model;

import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import softtech.apps.model.DbConstant;
import softtech.apps.utilities.Utilities;
public class DatabaseHandler extends SQLiteOpenHelper {

	Context myContext;
	private static String DB_PATH = "";
	private final String CREATE_USERS_TABLE = "CREATE TABLE " + DbConstant.TABLE_USERS
			+ "(" 
			+ DbConstant.USER_ID_CLIENT + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ DbConstant.USER_EMAIL + " TEXT,"
			+ DbConstant.USER_AVATAR_PATH + " TEXT,"
			+ DbConstant.USER_FULL_NAME + " TEXT,"
			+ DbConstant.USER_ADDRESS + " TEXT,"
			+ DbConstant.USER_PHONE_NUMBER + " TEXT,"
			+ DbConstant.USER_RATING + " REAL,"
			+ DbConstant.USER_TYPE + " INTEGER,"
			+ DbConstant.USER_ID_SERVER + " INTEGER)";

	private final String CREATE_MESSAGES_TABLE = "CREATE TABLE " + DbConstant.TABLE_MESSAGES
			+ "(" 
			+ DbConstant.MESSAGES_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ DbConstant.MESSAGES_SIDE + " INTEGER,"
			+ DbConstant.MESSAGES_CONTENT + " TEXT,"
			+ DbConstant.MESSAGES_FROM + " INTEGER,"
			+ DbConstant.MESSAGES_ID_SERVER + " INTEGER)";
	
	public DatabaseHandler(Context context) {
		super(context, DbConstant.DATABASE_NAME, null, DbConstant.DATABASE_VERSION);
		this.myContext = context;

		if (android.os.Build.VERSION.SDK_INT >= 4.2) {
			DB_PATH = myContext.getApplicationInfo().dataDir + "/databases/";
		} else {
			DB_PATH = "/data/data/" + myContext.getPackageName()
					+ "/databases/";
		}

		/*
		 * DB_PATH = Environment.getDataDirectory().getPath() + "/data/" +
		 * myContext.getPackageName() + "/databases/";
		 */
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		// creating required tables
		db.execSQL(CREATE_USERS_TABLE);	
		db.execSQL(CREATE_MESSAGES_TABLE);
		// Init with table Users
		db.execSQL("INSERT INTO " + DbConstant.TABLE_USERS + "(" 
				+ DbConstant.USER_EMAIL + "," 
				+ DbConstant.USER_AVATAR_PATH + "," 
				+ DbConstant.USER_FULL_NAME + ","
				+ DbConstant.USER_ADDRESS + ","
				+ DbConstant.USER_PHONE_NUMBER + ","
				+ DbConstant.USER_RATING + ","
				+ DbConstant.USER_TYPE + ","
				+ DbConstant.USER_ID_SERVER+ ") values("
				+ "'loicuoi@gmail.com'" + ","
				+ "'R.drawable.ic_laucher'" + ","
				+ "'Quach Van Quyet'" + ","
				+ "'17 Nguyen Duc Tinh'" + ","
				+ "'01275981368'" + ","
				+ 3.5 + ","
				+ 0 + ","
				+ 1 + 
				")");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// on upgrade drop older tables
		db.execSQL("DROP TABLE IF EXISTS " + DbConstant.TABLE_USERS);
		db.execSQL("DROP TABLE IF EXISTS " + DbConstant.TABLE_MESSAGES);
		// create new tables
		onCreate(db);
	}

	/**
	 * Xu ly tat cac van de lien quan den config o day Them sua, xoa config Cac
	 * thong tin ve cau hinh duoc dat co dinh va khong the xoa, chi co the sua
	 * */

	// Update config
	public int updateUser(User user) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(DbConstant.USER_EMAIL, user.getEmail());
		values.put(DbConstant.USER_AVATAR_PATH, user.getAvatar_path());
		values.put(DbConstant.USER_FULL_NAME, user.getFull_name());
		values.put(DbConstant.USER_ADDRESS, user.getAddress());
		values.put(DbConstant.USER_PHONE_NUMBER, user.getPhone_number());
		values.put(DbConstant.USER_RATING, user.getRating());
		values.put(DbConstant.USER_TYPE, user.getType());
		values.put(DbConstant.USER_ID_SERVER, user.getId_server());
		// updating row
		int temp = db.update(DbConstant.TABLE_USERS, values, DbConstant.USER_ID_CLIENT + " = ?",
				new String[] { String.valueOf(user.getId_client()) });
		db.close();
		return temp;
	}

	// Select config
	public User getUserById(int id_client) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(DbConstant.TABLE_USERS, new String[] { 
				DbConstant.USER_ID_CLIENT,
				DbConstant.USER_EMAIL,
				DbConstant.USER_AVATAR_PATH, 
				DbConstant.USER_FULL_NAME, 
				DbConstant.USER_ADDRESS, 
				DbConstant.USER_PHONE_NUMBER, 
				DbConstant.USER_RATING, 
				DbConstant.USER_TYPE, 
				DbConstant.USER_ID_SERVER
				}, DbConstant.USER_ID_CLIENT + "=?",
				new String[] { String.valueOf(id_client) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		User user = new User(
				Integer.parseInt(cursor.getString(0)),
				cursor.getString(1),
				cursor.getString(2),
				cursor.getString(3),
				cursor.getString(4),
				cursor.getString(5),
				Float.parseFloat(cursor.getString(6)),
				Integer.parseInt(cursor.getString(7)),
				Integer.parseInt(cursor.getString(8))
				);
		// return message
		db.close();
		return user;
	}

	/**
	 * Get User by Email
	 * 
	 * @param keyword
	 *            containt value
	 * @return Config Object | null
	 */
	public User getUserByEmail(String email) {

		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(DbConstant.TABLE_USERS, new String[] { 
				DbConstant.USER_ID_CLIENT,
				DbConstant.USER_EMAIL,
				DbConstant.USER_AVATAR_PATH, 
				DbConstant.USER_FULL_NAME, 
				DbConstant.USER_ADDRESS, 
				DbConstant.USER_PHONE_NUMBER, 
				DbConstant.USER_RATING, 
				DbConstant.USER_TYPE, 
				DbConstant.USER_ID_SERVER
				}, DbConstant.USER_EMAIL + "=?",
				new String[] { email }, null, null, null, " 1");
		if (cursor != null) {
			cursor.moveToFirst();

			User user = new User(
					Integer.parseInt(cursor.getString(0)),
					cursor.getString(1),
					cursor.getString(2),
					cursor.getString(3),
					cursor.getString(4),
					cursor.getString(5),
					Float.parseFloat(cursor.getString(6)),
					Integer.parseInt(cursor.getString(7)),
					Integer.parseInt(cursor.getString(8))
					);

			// return message
			db.close();
			return user;
		}

		return null;
	}

	/**
	 * insert User
	 * 
	 * @param cfg
	 *            want insert
	 * @return 0 if keyword of cfg exist else the row ID of the newly inserted
	 *         row
	 */

	public long insertUser(User user) {
		SQLiteDatabase db = this.getReadableDatabase();

		User exist = getUserByEmail(user.getEmail());
		if (exist == null) {
			ContentValues values = new ContentValues();
			values.put(DbConstant.USER_EMAIL, user.getEmail());
			values.put(DbConstant.USER_AVATAR_PATH, user.getAvatar_path());
			values.put(DbConstant.USER_FULL_NAME, user.getFull_name());
			values.put(DbConstant.USER_ADDRESS, user.getAddress());
			values.put(DbConstant.USER_PHONE_NUMBER, user.getPhone_number());
			values.put(DbConstant.USER_RATING, user.getRating());
			values.put(DbConstant.USER_TYPE, user.getType());
			values.put(DbConstant.USER_ID_SERVER, user.getId_server());
			long succes = db.insert(DbConstant.TABLE_USERS, null, values);
			db.close();
			return succes;
		}

		return 0;
	}
	// Getting All user
	public List<User> getAllUsers() {
		List<User> userList = new ArrayList<User>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + DbConstant.TABLE_USERS;
		try {
			SQLiteDatabase db = this.getWritableDatabase();
			Cursor cursor = db.rawQuery(selectQuery, null);

			// looping through all rows and adding to list
			if (cursor.moveToFirst()) {
				do {

					User user = new User(
							Integer.parseInt(cursor.getString(0)),
							cursor.getString(1),
							cursor.getString(2),
							cursor.getString(3),
							cursor.getString(4),
							cursor.getString(5),
							Float.parseFloat(cursor.getString(6)),
							Integer.parseInt(cursor.getString(7)),
							Integer.parseInt(cursor.getString(8))
							);
					userList.add(user);
				} while (cursor.moveToNext());
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		// return user list
		return userList;
	}
	
	
	// ###################################################################
	
	/**
	 * insert Message
	 */

	public long insertMessage(Conversation mes) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(DbConstant.MESSAGES_SIDE,mes.isLeft()); // Msg title
		values.put(DbConstant.MESSAGES_CONTENT,mes.getMessage()); // Msg content
		values.put(DbConstant.MESSAGES_FROM, mes.getUserIdFrom());// Msg set default
		values.put(DbConstant.MESSAGES_ID_SERVER, mes.getId_server());// Msg set default
		
		long id = db.insert(DbConstant.TABLE_MESSAGES, null, values);
		db.close(); // Closing database connection
		return id;
	}
	
	/**
	 * Get top X message by Offset
	 * @return List message by Offset
	 */
	public List<Conversation> getTopMessage(int limit,int offset,int fromUserId) {
		List<Conversation> messageList = new ArrayList<Conversation>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + DbConstant.TABLE_MESSAGES 
				+ " WHERE "+ DbConstant.MESSAGES_FROM+" = "+fromUserId
				+ " ORDER BY "
				+ DbConstant.MESSAGES_ID + " COLLATE NOCASE DESC LIMIT "+limit+" OFFSET "+offset;
				

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor queryMsg = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (queryMsg.moveToFirst()) {
			do {
				Conversation msg = new Conversation(
						queryMsg.getInt(0),
						queryMsg.getInt(1),
						queryMsg.getString(2),
						queryMsg.getInt(3),
						queryMsg.getInt(4)
						);
				// Adding message to list
				messageList.add(msg);
			} while (queryMsg.moveToNext());
		}
		db.close();
		// return message list
		return messageList;
	}
	
}